from DataAccess.DBConnFactory import DBConnFactory
from datetime import date, timedelta
from string import Template

from Misc.Utils import *

from TradeReport import *
from HldReport import *

def query_realized_pnl_average_cost(ref_date, portf_list = None, tick_list = None):

	start_date = date(2007,9,1)
	d = start_date
	one_day = timedelta(days = 1)
		
	crt_hld = {} # crt_hld[(ticker, portf_id)] = [holding, avg_cost]
	realized_pnl = [] #(trd_date, ticker, security_type, currency, portf_id, pnl, type, class)
	
	print 'running info...start calc realized pnl'
	
	while d <= ref_date:
		
		print '   calc realized pnl at', d.isoformat()
		
		#dividend
		sql_tpl = Template('''select dishty.symbol as ticker,
								SUM(nvl(dishty3,0))/10 as cash_divd, 
								securitycode.currency,
								SUM(nvl(dishty7,0)+nvl(dishty8,0))/10 as share_divd,
								SUM(nvl(DisHty4,0))/10 as cash_divd_ex
							from dishty 
							join securitycode 
								on dishty.symbol=securitycode.symbol 
								and securitycode.stype in ('EQA','EQB')
							where dishty13 = TO_DATE('${DATE}', 'yyyy-mm-dd')
							GROUP BY dishty.symbol,securitycode.currency
							union
							select ticker, cash_divd, currency, share_divd, cash_divd_ex
							  from HKSTOCK_DIVD
							 where currency = 'HKD'
							   and ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')''')
		sql_text = sql_tpl.substitute(DATE = d.isoformat())
		cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
		r = cursor.execute(sql_text).fetchall()
		divd = {}
		for elem in r:
			divd[elem[0]] = (elem[1], elem[2], elem[3], elem[4]) #(cash_divd,currency,share_divd,cash_divd_ex)
			
		if divd:
			for k, v in crt_hld.iteritems():
				if divd.has_key(k[0]) and v[0] != 0:
					currency = divd[k[0]][1]
					share_dvd = divd[k[0]][2]
					cash_dvd = divd[k[0]][0] if v[0] < 0 else divd[k[0]][3]
					
					if cash_dvd and cash_dvd > 0:
						#crt_hld[k][1] = crt_hld[k][1] - cash_dvd
						pnl_dvd = round(crt_hld[k][0]*cash_dvd, 2)
						realized_pnl.append((d, k[0], '', currency, k[1], pnl_dvd, 'DIVD', 'REALIZED'))					
					
					if share_dvd and share_dvd > 0:
						crt_hld[k][1] *= crt_hld[k][0]/(crt_hld[k][0] + round(crt_hld[k][0]*share_dvd))
						crt_hld[k][0] += round(crt_hld[k][0]*share_dvd)

		#trade
		trd = query_trade(d, d, portf_list, tick_list)
		
		for elem in trd:
			ticker = elem[3]
			trd_amount = elem[7] if (elem[6] == 'BUY' or elem[6] == 'SHORT COVERING') else -elem[7]
			trd_cost = elem[8]
			security_type = elem[5]
			currency = elem[9]
			portf_id = elem[11]
			point_value = 1.0
				
			#futures
			if security_type == 'FUTURES':
				sql_tpl = Template('''select "value" from comm_misc_static_info
									where field = 'FUT_VAL_PT'
									and ticker='${TICKER}' ''')
				sql_text = sql_tpl.substitute(TICKER = ticker)
				cursor = DBConnFactory().get_db_connection('PKEDB').cursor()				
				r = cursor.execute(sql_text).fetchall()	
				if r:
					point_value = r[0][0]			
				#
			if crt_hld.has_key((ticker, portf_id)):
				holding = crt_hld[(ticker, portf_id)][0]
				avg_cost = crt_hld[(ticker, portf_id)][1]
					
				crt_hld[(ticker, portf_id)][0] += trd_amount
					
				if holding*trd_amount >= 0:
					crt_hld[(ticker, portf_id)][1] = (holding*avg_cost + trd_amount*trd_cost)/(holding + trd_amount)
				
				else:
					pnl = -trd_amount/abs(trd_amount)* min(abs(holding),abs(trd_amount))*(trd_cost - avg_cost)*point_value
					realized_pnl.append((d, ticker, security_type, currency, portf_id, pnl, 'TRADE', 'REALIZED'))

					if abs(holding) < abs(trd_amount):
						crt_hld[(ticker, portf_id)][1] = trd_cost	
			else:
				crt_hld.setdefault((ticker, portf_id),[trd_amount, trd_cost])
			
		d += one_day
		
	print 'running info...start calc unrealized pnl'
	
	#unrealized pnl
	hld = query_hld(ref_date, portf_list, tick_list)
	
	for elem in hld:
		ticker = elem[0]
		hld_amount = elem[4]
		price_local = elem[7]
		security_type = elem[2]
		currency = elem[17]
		portf_id = elem[20]
		point_value = 1.0
		
		if security_type == 'CASH' or security_type == 'REPO':
			continue
		
		#futures
		if security_type == 'FUTURES':
			sql_tpl = Template('''select "value" from comm_misc_static_info
								where field = 'FUT_VAL_PT'
								and ticker='${TICKER}' ''')
			sql_text = sql_tpl.substitute(TICKER = ticker)
			cursor = DBConnFactory().get_db_connection('PKEDB').cursor()				
			r = cursor.execute(sql_text).fetchall()	
			if r:
				point_value = r[0][0]
		
		if crt_hld.has_key((ticker, portf_id)) and crt_hld[(ticker, portf_id)][0] == hld_amount:		
			avg_cost = crt_hld[(ticker, portf_id)][1]
			pnl = hld_amount*(price_local - avg_cost)*point_value
			realized_pnl.append((ref_date, ticker, security_type, currency, portf_id, pnl, '', 'UNREALIZED'))			
		else:
			print '   warning info, holding unmatched,', portf_id, ticker
	
	return realized_pnl
	
def query_realized_pnl_first_in_first_out(ref_date, portf_list = None, tick_list = None):
	
	start_date = date(2007, 9, 1)
	d = start_date
	one_day = timedelta(days = 1)
		
	crt_hld = {} # crt_hld[(ticker, portf_id)] = holding
	rmd_trd = {} # rmd_trd[(ticker, portf_id)] = [[trd_date, trd_amount, trd_cost, security_type, currency, is_dead]]
	realized_pnl = [] #(trd_date, ticker, security_type, currency, portf_id, pnl, type, class, horizon, cost)
	
	print 'running info...start calc realized pnl'
	
	while d <= ref_date:
	
		print '   calc realized pnl at', d.isoformat()
		
		#dividend
		sql_tpl = Template('''select dishty.symbol as ticker,
								SUM(nvl(dishty3,0))/10 as cash_divd, 
								securitycode.currency,
								SUM(nvl(dishty7,0)+nvl(dishty8,0))/10 as share_divd,
								SUM(nvl(DisHty4,0))/10 as cash_divd_ex
							from dishty 
							join securitycode 
								on dishty.symbol=securitycode.symbol 
								and securitycode.stype in ('EQA','EQB')
							where dishty13 = TO_DATE('${DATE}', 'yyyy-mm-dd')
							GROUP BY dishty.symbol,securitycode.currency
							union
							select ticker, cash_divd, currency, share_divd, cash_divd_ex
							  from HKSTOCK_DIVD
							 where currency = 'HKD'
							   and ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')''')
		sql_text = sql_tpl.substitute(DATE = d.isoformat())
		cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
		r = cursor.execute(sql_text).fetchall()
		divd = {}
		for elem in r:
			divd[elem[0]] = (elem[1], elem[2], elem[3], elem[4]) #(cash_divd, currency, share_divd, cash_divd_ex)
			
		if divd:
		
			for k, v in rmd_trd.iteritems():		
				if divd.has_key(k[0]):
					share_dvd = divd[k[0]][2]	
					if crt_hld[k] != 0 and share_dvd and share_dvd > 0:

						#regard share dividend as trade with zero cost
						rmd_trd[k].append([d, round(crt_hld[k]*share_dvd), 0.0, rmd_trd[k][0][3], rmd_trd[k][0][4], 0])
						
						#adjust quantity and cost for remain trades
						# for i in range(len(v)):
							# if rmd_trd[k][i][5] == 0:
								# rmd_trd[k][i][2] *= rmd_trd[k][i][1]/(rmd_trd[k][i][1] + round(rmd_trd[k][i][1]*share_dvd))
								# rmd_trd[k][i][1] += round(rmd_trd[k][i][1]*share_dvd)
			
			for k, v in crt_hld.iteritems():
				if divd.has_key(k[0]) and v != 0:
					currency = divd[k[0]][1]
					share_dvd = divd[k[0]][2]
					cash_dvd = divd[k[0]][0] if v < 0 else divd[k[0]][3]
					
					if cash_dvd and cash_dvd > 0:
						pnl_dvd = round(crt_hld[k]*cash_dvd, 2)
						realized_pnl.append((d, k[0], '', currency, k[1], pnl_dvd, 'DIVD', 'REALIZED', 0, 0))					
					
					if share_dvd and share_dvd > 0:
						crt_hld[k] += round(crt_hld[k]*share_dvd)
						
		#trade
		trd = query_trade(d, d, portf_list, tick_list)
		trd_agrgt = {}
		for elem in trd:
			trd_amount = elem[7] if (elem[6] == 'BUY' or elem[6] == 'SHORT COVERING') else -elem[7]
			trd_cost = elem[8]
			if trd_agrgt.has_key((elem[3], elem[11])):
				pre_trd_amount = trd_agrgt[(elem[3], elem[11])][2]
				pre_trd_cost = trd_agrgt[(elem[3], elem[11])][3]
				trd_agrgt[(elem[3], elem[11])][2] += trd_amount
				if (pre_trd_amount + trd_amount) != 0:
					trd_agrgt[(elem[3], elem[11])][3] = (pre_trd_amount*pre_trd_cost + trd_amount*trd_cost)/(pre_trd_amount + trd_amount)
				else:
					trd_agrgt[(elem[3], elem[11])][3] = 0
			else:
				trd_agrgt[(elem[3], elem[11])] = [elem[5], elem[6], trd_amount, trd_cost, elem[9]]
		
		for k, v in trd_agrgt.iteritems():
			
			if v[2] == 0:
				continue
				
			currency = v[4]
			fx = query_latest_fx(d, currency, 'CNY') if (currency == 'USD' or currency == 'HKD') else 1.0
			ticker = k[0]
			trd_amount = v[2]
			#trd_cost = v[3]*fx/(1 + 8.0/1000) if (v[1] == 'BUY' or v[1] == 'SHORT COVERING') else v[3]*fx/(1 - 9.0/1000)
			trd_cost = v[3]
			security_type = v[0]
			portf_id = k[1]
			point_value = 1.0
			
			#futures
			if security_type == 'FUTURES':
				sql_tpl = Template('''select "value" from comm_misc_static_info
									where field = 'FUT_VAL_PT'
									and ticker='${TICKER}' ''')
				sql_text = sql_tpl.substitute(TICKER = ticker)
				cursor = DBConnFactory().get_db_connection('PKEDB').cursor()				
				r = cursor.execute(sql_text).fetchall()	
				if r:
					point_value = r[0][0]			
			#
			if crt_hld.has_key((ticker, portf_id)):			
				crt_hld[(ticker, portf_id)] += trd_amount			
			else:
				crt_hld[(ticker, portf_id)] = trd_amount
			#	
			if rmd_trd.has_key((ticker, portf_id)):
				
				is_new = True
				
				for i in range(len(rmd_trd[(ticker, portf_id)])):
				
					if rmd_trd[(ticker, portf_id)][i][5] == 0:

						is_new = False
						rmd_d = rmd_trd[(ticker, portf_id)][i][0]
						rmd_amount = rmd_trd[(ticker, portf_id)][i][1]
						rmd_cost = rmd_trd[(ticker, portf_id)][i][2]
					
						if trd_amount == 0:
							break
					
						if rmd_amount*trd_amount > 0:
							rmd_trd[(ticker, portf_id)].append([d, trd_amount, trd_cost, security_type, currency, 0])
							break
						
						if abs(rmd_amount) > abs(trd_amount):
							pnl = -trd_amount*(trd_cost - rmd_cost)*point_value
							realized_pnl.append((d, ticker, security_type, currency, portf_id, pnl, 'TRADE: ' + rmd_d.isoformat(), 'REALIZED', (d - rmd_d).days, trd_amount*rmd_cost))
							rmd_trd[(ticker, portf_id)][i][1] += trd_amount
							break
							
						if abs(rmd_amount) <= abs(trd_amount):
							pnl = rmd_amount*(trd_cost - rmd_cost)*point_value
							realized_pnl.append((d, ticker, security_type, currency, portf_id, pnl, 'TRADE: ' + rmd_d.isoformat(), 'REALIZED', (d - rmd_d).days, rmd_amount*rmd_cost))
							trd_amount += rmd_amount
							rmd_trd[(ticker, portf_id)][i][5] = 1
				
				if is_new == True:
					rmd_trd[(ticker, portf_id)].append([d, trd_amount, trd_cost, security_type, currency, 0])
			else:
				rmd_trd[(ticker, portf_id)] = [[d, trd_amount, trd_cost, security_type, currency, 0]]
				
		d += one_day
	
	print 'running info...start calc unrealized pnl'
	
	#unrealized pnl
	hld = query_hld(ref_date, portf_list, tick_list)
	
	for elem in hld:
		ticker = elem[0]
		hld_amount = elem[4]
		price_local = elem[7]
		security_type = elem[2]
		currency = elem[17]
		portf_id = elem[20]
		point_value = 1.0
		
		if security_type == 'CASH' or security_type == 'REPO':
			continue
		
		#futures
		if security_type == 'FUTURES':
			sql_tpl = Template('''select "value" from comm_misc_static_info
								where field = 'FUT_VAL_PT'
								and ticker='${TICKER}' ''')
			sql_text = sql_tpl.substitute(TICKER = ticker)	
			cursor = DBConnFactory().get_db_connection('PKEDB').cursor()			
			r = cursor.execute(sql_text).fetchall()	
			if r:
				point_value = r[0][0]
		
		if rmd_trd.has_key((ticker, portf_id)):
		
			for elem in rmd_trd[(ticker, portf_id)]:
			
				if elem[5] == 0:
					rmd_d = elem[0]
					pnl = elem[1]*(price_local - elem[2])*point_value
					realized_pnl.append((ref_date, ticker, security_type, currency, portf_id, pnl, '', 'UNREALIZED', (ref_date - rmd_d).days, elem[1]*elem[2]))
					hld_amount -= elem[1]
				
			if hld_amount != 0:
				print '   warning info, holding unmatched,', portf_id, ticker, hld_amount
			
		else:
			print '   warning info, holding unmatched,', portf_id, ticker
			
	return realized_pnl
	
def query_realized_pnl_first_in_first_out_old(ref_date, portf_list = None, tick_list = None):
	
	start_date = date(2007,9,1)
	d = start_date
	one_day = timedelta(days = 1)
		
	crt_hld = {} # crt_hld[(ticker, portf_id)] = holding
	rmd_trd = {} # rmd_trd[(ticker, portf_id)][trd_date] = [trd_amount, trd_cost, security_type, currency]
	realized_pnl = [] #(trd_date, ticker, security_type, currency, portf_id, pnl, type, class)
	
	print 'running info...start calc realized pnl'
	
	while d <= ref_date:
	
		print '   calc realized pnl at', d.isoformat()
		
		#dividend
		sql_tpl = Template('''select dishty.symbol as ticker,
								SUM(nvl(dishty3,0))/10 as cash_divd, 
								securitycode.currency,
								SUM(nvl(dishty7,0)+nvl(dishty8,0))/10 as share_divd,
								SUM(nvl(DisHty4,0))/10 as cash_divd_ex
							from dishty 
							join securitycode 
								on dishty.symbol=securitycode.symbol 
								and securitycode.stype in ('EQA','EQB')
							where dishty13 = TO_DATE('${DATE}', 'yyyy-mm-dd')
							GROUP BY dishty.symbol,securitycode.currency
							union
							select ticker, cash_divd, currency, share_divd, cash_divd_ex
							  from HKSTOCK_DIVD
							 where currency = 'HKD'
							   and ref_date = TO_DATE('${DATE}', 'yyyy-mm-dd')''')
		sql_text = sql_tpl.substitute(DATE = d.isoformat())
		cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
		r = cursor.execute(sql_text).fetchall()
		divd = {}
		for elem in r:
			divd[elem[0]] = (elem[1], elem[2], elem[3], elem[4])
			
		if divd:
			for k, v in rmd_trd.iteritems():
				if divd.has_key(k[0]):
					share_dvd = divd[k[0]][2]
					if share_dvd and share_dvd > 0:
					
						rmd_trd[k][d] = [round(crt_hld[k]*share_dvd), 0.0, '', '']
						
						# for k1, v1 in v.iteritems():
							# rmd_trd[k][k1][1] *= rmd_trd[k][k1][0]/(rmd_trd[k][k1][0] + round(rmd_trd[k][k1][0]*share_dvd))
							# rmd_trd[k][k1][0] += round(rmd_trd[k][k1][0]*share_dvd)
		
			for k, v in crt_hld.iteritems():
				if divd.has_key(k[0]) and v != 0:
					currency = divd[k[0]][1]
					share_dvd = divd[k[0]][2]
					cash_dvd = divd[k[0]][0] if v < 0 else divd[k[0]][3]
					
					if cash_dvd and cash_dvd > 0:
						pnl_dvd = round(crt_hld[k]*cash_dvd, 2)
						realized_pnl.append((d, k[0], '', currency, k[1], pnl_dvd, 'DIVD', 'REALIZED'))					
					
					if share_dvd and share_dvd > 0:
						crt_hld[k] += round(crt_hld[k]*share_dvd)
						
		#trade
		trd = query_trade(d, d, portf_list, tick_list)
		trd_agrgt = {}
		for elem in trd:
			trd_amount = elem[7] if (elem[6] == 'BUY' or elem[6] == 'SHORT COVERING') else -elem[7]
			trd_cost = elem[8]
			if trd_agrgt.has_key((elem[3], elem[11])):
				pre_trd_amount = trd_agrgt[(elem[3], elem[11])][2]
				pre_trd_cost = trd_agrgt[(elem[3], elem[11])][3]
				trd_agrgt[(elem[3], elem[11])][2] += trd_amount
				if (pre_trd_amount + trd_amount) != 0:
					trd_agrgt[(elem[3], elem[11])][3] = (pre_trd_amount*pre_trd_cost + trd_amount*trd_cost)/(pre_trd_amount + trd_amount)
				else:
					trd_agrgt[(elem[3], elem[11])][3] = 0
			else:
				trd_agrgt[(elem[3], elem[11])] = [elem[5], elem[6], trd_amount, trd_cost, elem[9]]
		
		for k, v in trd_agrgt.iteritems():
			
			if v[2] == 0:
				continue
				
			currency = v[4]
			fx = query_latest_fx(d, currency, 'CNY') if (currency == 'USD' or currency == 'HKD') else 1.0
			ticker = k[0]
			trd_amount = v[2]
			#trd_cost = v[3]*fx/(1 + 8.0/1000) if (v[1] == 'BUY' or v[1] == 'SHORT COVERING') else v[3]*fx/(1 - 9.0/1000)
			trd_cost = v[3]
			security_type = v[0]
			portf_id = k[1]
			point_value = 1.0
			
			#futures
			if security_type == 'FUTURES':
				sql_tpl = Template('''select "value" from comm_misc_static_info
									where field = 'FUT_VAL_PT'
									and ticker='${TICKER}' ''')
				sql_text = sql_tpl.substitute(TICKER = ticker)
				cursor = DBConnFactory().get_db_connection('PKEDB').cursor()				
				r = cursor.execute(sql_text).fetchall()	
				if r:
					point_value = r[0][0]			
			#
			if crt_hld.has_key((ticker, portf_id)):			
				crt_hld[(ticker, portf_id)] += trd_amount			
			else:
				crt_hld[(ticker, portf_id)] = trd_amount
			#	
			if rmd_trd.has_key((ticker, portf_id)) and rmd_trd[(ticker, portf_id)]:
				
				sorted_rmd_trd = sorted(rmd_trd[(ticker, portf_id)].items(), key = lambda x:x[0])
			
				for dt, elem in sorted_rmd_trd:
				
					if trd_amount == 0:
						break
				
					if elem[0]*trd_amount > 0:
					
						if rmd_trd[(ticker, portf_id)].has_key(d):
							print '   err info, trade has existed', portf_id, ticker, d
							
						rmd_trd[(ticker, portf_id)][d] = [trd_amount, trd_cost, security_type, currency]
						break
					
					if abs(elem[0]) > abs(trd_amount):
						pnl = -trd_amount*(trd_cost - elem[1])*point_value
						realized_pnl.append((d, ticker, security_type, currency, portf_id, pnl, 'TRADE: ' + dt.isoformat(), 'REALIZED'))
						rmd_trd[(ticker, portf_id)][dt][0] += trd_amount
						break
						
					if abs(elem[0]) <= abs(trd_amount):
						pnl = elem[0]*(trd_cost - elem[1])*point_value
						realized_pnl.append((d, ticker, security_type, currency, portf_id, pnl, 'TRADE: ' + dt.isoformat(), 'REALIZED'))
						trd_amount += elem[0]
						del rmd_trd[(ticker, portf_id)][dt]		
			else:
				rmd_trd[(ticker, portf_id)] = {d: [trd_amount, trd_cost, security_type, currency]}
				
		d += one_day
	
	print 'running info...start calc unrealized pnl'
	
	#unrealized pnl
	hld = query_hld(ref_date, portf_list, tick_list)
	
	for elem in hld:
		ticker = elem[0]
		hld_amount = elem[4]
		price_local = elem[7]
		security_type = elem[2]
		currency = elem[17]
		portf_id = elem[20]
		point_value = 1.0
		
		if security_type == 'CASH' or security_type == 'REPO':
			continue
		
		#futures
		if security_type == 'FUTURES':
			sql_tpl = Template('''select "value" from comm_misc_static_info
								where field = 'FUT_VAL_PT'
								and ticker='${TICKER}' ''')
			sql_text = sql_tpl.substitute(TICKER = ticker)	
			cursor = DBConnFactory().get_db_connection('PKEDB').cursor()			
			r = cursor.execute(sql_text).fetchall()	
			if r:
				point_value = r[0][0]
		
		if rmd_trd.has_key((ticker, portf_id)):
				
			sorted_rmd_trd = sorted(rmd_trd[(ticker, portf_id)].items(), key = lambda x:x[0])
			
			for dt, elem in sorted_rmd_trd:
				pnl = elem[0]*(price_local - elem[1])*point_value
				realized_pnl.append((ref_date, ticker, security_type, currency, portf_id, pnl, '', 'UNREALIZED'))
				hld_amount -= elem[0]
				
			if hld_amount != 0:
				print '   warning info, holding unmatched,', portf_id, ticker, hld_amount
			
		else:
			print '   warning info, holding unmatched,', portf_id, ticker
			
	return realized_pnl